package com.ch7.dal;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.jws.soap.SOAPBinding.Use;
import javax.naming.spi.DirStateFactory.Result;
import javax.xml.registry.infomodel.User;

import com.ch7.common.Conn;
import com.ch7.common.MD5;
import com.ch7.model.UserInfo;

public class Users {
	
	Conn conn = new Conn();
	
	public List<UserInfo> getList() throws SQLException{
		List<UserInfo> list = new ArrayList<UserInfo>();
		String sql="select * from users order by username asc";
		ResultSet rs=conn.executeQuery(sql);
		while(rs.next()){
			UserInfo info= new UserInfo();
			info.setUsername(rs.getString("UserName"));
			info.setPassword(rs.getString("Password"));
			info.setEmail(rs.getString("Email"));
			info.setPower(rs.getString("Power"));
			list.add(info);
			
		}
		conn.close();
		return list;
		
	}
	
	public boolean isExist(String username,String password) throws SQLException{
		boolean result=false;
		UserInfo info = new UserInfo();
		String sql="select * from users u where UserName='"+username+"' and Password='"+password+"'";
		System.out.println(sql);
		ResultSet rs=conn.executeQuery(sql);
		if(rs.next()){
			info.setUsername(rs.getString("UserName"));
			info.setPassword(rs.getString("Password"));
			info.setEmail(rs.getString("Email"));
			info.setPower(rs.getString("Power"));
			result=true;
		}
		conn.close();
		return result;
	}
	
	public UserInfo getUsersInfo(String username) throws SQLException{
		UserInfo info = new UserInfo();
		String sql="select * from users U where UserName='"+username+"'";
		ResultSet rs=conn.executeQuery(sql);
		if(rs.next()){
			info.setUsername(rs.getString("UserName"));
			info.setPassword(rs.getString("Password"));
			info.setEmail(rs.getString("Email"));
			info.setPower(rs.getString("Power"));
		
		}
		conn.close();
		return info;
	}
	
	public boolean isExistUsersInfo(String username) throws SQLException{
		boolean result =false;
		UserInfo info =new UserInfo();
		String sql="select * from users U where UserName='"+username+"'";
		ResultSet rs=conn.executeQuery(sql);
		if(rs.next()){
			info.setUsername(rs.getString("UserName"));
			info.setPassword(rs.getString("Password"));
			info.setEmail(rs.getString("Email"));
			info.setPower(rs.getString("Power"));
			result=true;
		}
		conn.close();
		return result;
	}
	
	public int insert(UserInfo info){
		String sql="insert into users(UserName,Password,Email,Power) values";
		sql=sql+"('"+info.getUsername()+"','"+info.getPassword()+"','"+info.getEmail()
		+"','"+info.getPower()+"')";
		System.out.println(sql);
		int result=0;
		result=conn.executeUpdate(sql);
		conn.close();
		return result;
	}

	public int update(UserInfo info){
		String sql="update users set Password='"+MD5.Encrypt(info.getPassword())+"',Email='"+info.getEmail()
		+"',Power='"+info.getPower()+"' where UserName='"+info.getUsername()+"'";
		int result =0;
		System.out.println(sql);
		result=conn.executeUpdate(sql);
		conn.close();
		return result;
	}
	
	public int delete(String name){
		String sql="delete from users where UserName='"+name+"'";
		int result =0;
		result=conn.executeUpdate(sql);
		conn.close();
		return result;
	}
}
